﻿//setting up web service
var http = require("http");
var port = process.env.port || 1337;
var host = '127.0.0.1';
http.createServer(function (req, res) {
    var idPatt = "[0-9]+"; //declare the variable for page and userid
    //I use '/' instead of '?'
    var patt1 = new RegExp("/topActiveUsers/page=" + idPatt);
    var patt2 = new RegExp("/users/id=" + idPatt);
    if (patt1.test(req.url)) { //  /topActiveUsers/page=xxx
        patt1 = new RegExp(idPatt);
        var page = patt1.exec(req.url);
        topActiveUsers(req, res, page);
    }
    else if (patt2.test(req.url)) { // /users/id=xxx
        patt2 = new RegExp(idPatt);
        var userid = patt2.exec(req.url);
        users(req, res, userid);
    }   
}).listen(port);
console.log("Connected to " + port + "   " + host);

//connect to postgresql database. I installed the pg package.
var pg = require("pg");
var conString = "pg://kykkzfrk12:nvewovgujq@assignment.codsssqklool.eu-central-1.rds.amazonaws.com:5432/kykkzfrk12_db";
var client = new pg.Client(conString);
client.connect();

//get topActiveUsers function
var topActiveUsers = function (req, res,page) {
    var offset = parseInt(page, 10) - 1; // I set one item per page
    var query4 = client.query("SELECT u.id as userid, u.created_at, u.name, COUNT(a.id) as count, json_agg(DISTINCT l.name) as listings FROM users u, applications a, listings l WHERE u.id = a.user_id AND l.id = a.listing_id AND a.created_at >= current_timestamp - interval '1 week' GROUP BY u.id ORDER BY count DESC LIMIT 1 OFFSET " + offset);
    query4.on("row", function (row, result) {
        result.addRow(row);
    });
    query4.on("end", function (result) {
        console.log(JSON.stringify(result.rows, null, "    "));
        res.writeHead(200, { 'Content-Type': 'text/plain' });
        res.write(JSON.stringify(result.rows) + "\n");
        res.end();
        client.end();
    });
};

//get users details function
var users = function (req, res,id) {
    var query5 = client.query("SELECT u.id as userid, u.created_at, u.name, (array_agg(DISTINCT('id:' || t.company_id || ',' || chr(10) || 'createdAt:' || c.created_at || ',' || chr(10) || 'name:' || c.name || ',' || chr(10) || 'isContact:' || contact_user)))[1:5] as companies , array_agg(DISTINCT('id:' || l.id || ',' || chr(10) || 'createdAt:' || l.created_at || ',' || chr(10) || 'name:' || l.name || ',' || chr(10) || 'description' || l.description)) as createdListings, array_agg(DISTINCT('id:' || a2.id || ',' || chr(10) || 'createdAt:' || a2.created_at || ',' || chr(10) || 'listing: {' || chr(10) || 'id:' || l2.id || ',' || chr(10) || 'name:' || l2.name || ',' || chr(10) || 'description:' || l2.description || ',' || chr(10) || 'coverLetter:' || a2.cover_letter || chr(10) || '}')) as applications FROM users u INNER JOIN teams t ON t.user_id = u.id INNER JOIN companies c ON t.company_id = c.id INNER JOIN listings l ON l.created_by = u.id INNER JOIN applications a ON u.id = a.user_id INNER JOIN applications a2 ON u.id = a2.user_id INNER JOIN listings l2 ON a2.listing_id = l2.id WHERE u.id = $1 GROUP BY u.id",id);
    query5.on("row", function (row, result) {
        result.addRow(row);
    });
    query5.on("end", function (result) {
        console.log(JSON.stringify(result.rows, null, "    "));
        res.writeHead(200, { 'Content-Type': 'text/plain' });
        res.write(JSON.stringify(result.rows) + "\n");
        res.end();
        client.end();
    });
};

